1. Technical Field
The present invention relates generally to the field of information processing by digital computers, and more particularly to a method and system, in an electronic spreadsheet, for computing statistical data on a set of values taken over time either by a given cell or by a pair of cells.
2. Related Art
Before computers, numerical analyses, particularly financial analyses, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerized replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this “pad” exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-left-hand corner), for example, the user types in “A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsbomeIMcGraw-Mll, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require the recalculation of every relationship in the model with each change made. Electronic spreadsheet systems are well suited to solve “what-if’ problems, that is, changing an input and seeing what happens to an output.
Electronic spreadsheets have become a tool of choice for computing statistics from a set of data. For a set of data arranged in a column, conventional electronic spreadsheet tools include means, in the form of statistical functions, to compute the average of the data set, or the maximum value of the data set, or the minimum value of the data set, or the standard deviation of the data set. For a couple of data ranges arranged for instance as a pair of columns, conventional electronic spreadsheet tools include means, in the form of statistical functions, to compute the covariance of the two data sets, or the correlation of the two data sets. When electronic spreadsheets are used to perform simulations of a model (business model, or industrial model, or nature life model), these statistical functions are very useful for deriving statistical information relative to the different simulation instances of the modelled process. Typically such simulations are performed by feeding a model with inputs which can either be randomly generated, or imported from external sources. Unfortunately these statistical functions operate on the base of a set of data, recorded within data ranges, so that each simulation must be first properly recorded onto a simulation result array, to eventually build a complete array of results upon which the statistical functions can operate. When the number of simulations increases (this is required to increase the confidence in the statistical results), the volume of information that may result may be prohibitive (and even reach the built-in limitations of the commercially available electronic spreadsheet tools). As the desired information relies to the statistical data, and not to each individual simulation output, the conventional statistical means available in electronic spreadsheet environment, are not suited for computing simulation statistics.